package dbutils;

import javax.swing.*;
import java.io.FileOutputStream;
import java.io.OutputStreamWriter;
import java.io.PrintWriter;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

/**
 * Created by IntelliJ IDEA.
 * User: ADMIN
 * Date: 09.03.2011
 * Time: 9:51:06
 * To change this template use File | Settings | File Templates.
 */
public class TransportValuesToDicGeneral {
    static List<TableField> listTableFields = new ArrayList<TableField>();
    static PrintWriter pw;

    public TransportValuesToDicGeneral() {
//        listTableFields = getTableFields();
//        System.out.println(" size "+listTableFields.size());
//        for(int i =0;i<listTableFields.size();i++){
//            System.out.println("tableName :"+listTableFields.get(i).getTableName()+
//                    " fieldName: "+listTableFields.get(i).getFieldName()+
//            " dicType :"+listTableFields.get(i).getDicType());
//           for(int k=0;k<listTableFields.get(i).getListValues().size();k++){
//               System.out.println(" name_ru: "+listTableFields.get(i).getListValues().get(k).get(0)+
//               " name_en : "+listTableFields.get(i).getListValues().get(k).get(1)+
//               " name_kk : "+listTableFields.get(i).getListValues().get(k).get(2));
//           }
//        }
    }

    public static List<TableField> getTableFields() {
        List<TableField> toReturn = new ArrayList<TableField>();
        String query = "SELECT TABLE_NAME,COLUMN_NAME,REFERENCED_TABLE_NAME " +
                "FROM information_schema.KEY_COLUMN_USAGE AS kcu " +
                "WHERE kcu.TABLE_SCHEMA='fs1' " +
                "AND kcu.REFERENCED_TABLE_NAME " +
                "IN(SELECT LOWER(unit)" +
                "FROM dic_units " +
                "WHERE unit LIKE 'DIC_%') " +
                "ORDER BY TABLE_NAME";
        try {
            Connection conn = DbUtils.getDBConnectionGeneral();
            PreparedStatement ps = conn.prepareStatement(query);
//            System.out.println("ps: "+ps.toString());
            ResultSet rs = ps.executeQuery();
            while (rs.next()) {
                TableField tableField = new TableField();
                tableField.setTableName(rs.getString("TABLE_NAME"));
                tableField.setFieldName(rs.getString("COLUMN_NAME"));
                tableField.setDicType(rs.getString("REFERENCED_TABLE_NAME"));
                tableField.setListValues(TransportValuesToDicGeneral.getListTranslateFromDic(tableField.getDicType()));
                toReturn.add(tableField);
            }
        } catch (Exception exp) {
            exp.printStackTrace();
        }
        return toReturn;
    }

    public static List<List<String>> getListTranslateFromDic(String tableName) {
        List<List<String>> toReturn = new ArrayList<List<String>>();
        try {
            Connection conn = DbUtils.getDBConnectionGeneral();
            PreparedStatement ps = conn.prepareStatement("SELECT * FROM " + tableName);
//            System.out.println("ps : "+ps.toString());
            ResultSet rs = ps.executeQuery();
            while (rs.next()) {
                List<String> tempList = new ArrayList<String>();
                tempList.add(rs.getString(1));
                tempList.add(rs.getString("name_ru"));
                tempList.add(rs.getString("name_en"));
                tempList.add(rs.getString("name_kk"));
                toReturn.add(tempList);

            }

        } catch (Exception exp) {
            exp.printStackTrace();
        }
        return toReturn;
    }

    public static List<String> generateInsertQueries(List<TableField> tableField) {
        List<String> insertQuery = new ArrayList<String>();
        for (int i = 0; i < tableField.size(); i++) {
            for (int k = 0; k < tableField.get(i).getListValues().size(); k++) {
                insertQuery.add("INSERT INTO " +
                        "  dic_general " +
                        "(dic_id,table_name,table_field,dic_type,name_ru,name_en,name_kk,user,insert_date) " +
                        "VALUES ('" +
                        tableField.get(i).getListValues().get(k).get(0) + "','" +
                        tableField.get(i).getTableName() + "','" +
                        tableField.get(i).getFieldName() + "','" +
                        tableField.get(i).getDicType() + "','" +
                        tableField.get(i).getListValues().get(k).get(1) + "','" +
                        tableField.get(i).getListValues().get(k).get(2) + "','" +
                        tableField.get(i).getListValues().get(k).get(3) + "'," +
                        "'admin',CURDATE());");
            }

        }
        return insertQuery;
    }

    public static void main(String[] args) {
//        TransportValuesToDicGeneral t = new TransportValuesToDicGeneral();
//        for(TableField tf:TransportValuesToDicGeneral.getTableFields()){
//            System.out.println("ALTER TABLE "+tf.getTableName()+
//                    " ADD FOREIGN KEY("+tf.getFieldName()+") REFERENCES dic_general(dic_id);");
//        }

        List<String> listQueries = TransportValuesToDicGeneral.generateInsertQueries(TransportValuesToDicGeneral.getTableFields());
        try {
            pw = new PrintWriter   // класс с методами записи строк
                    (new OutputStreamWriter          // класс-преобразователь
                            (new FileOutputStream         // класс записи байтов в файл
                                    ("C:/queryFile.sql"), "utf-8"));
        } catch (Exception exp) {
            exp.printStackTrace();
        }
        for (String str : listQueries) {
//            System.out.println(str);
            pw.println(str);  // записываем строку в файл
        }
        JOptionPane.showMessageDialog(null, "file C:/queryFile.sql created successfully");
        pw.close();

    }

}
